﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using MallInteractiveMap.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;

namespace MallInteractiveMap.ServerDataHandler
{
    public abstract class BaseDatabaseHandler
    {
        public static string CONNECTIONSTRING;
        static BaseDatabaseHandler()
        {
            CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["MallInteractiveMapDB"].ConnectionString;
            //CONNECTIONSTRING = @"Data Source=setu-pc\sqlexpress;Initial Catalog=MallInteractiveMap; User ID=sa; Password=raas;";
           // CONNECTIONSTRING = @"Data Source=saraf-vaio\sqlexpress;Initial Catalog=MallInteractiveMap_28_July; User ID=sarafuddin; Password=sarafsaraf;";

        }
        protected Database GetSQLDatabase()
        {
            return new SqlDatabase(CONNECTIONSTRING);
        }

        protected DataSet GetSPResultSetEx(string storedProcName, QueryParamList spParams, ref string pErrString)
        {
            Database db = GetSQLDatabase();
            DataSet ds = new DataSet();
            try
            {

                using (DbCommand cmd = db.GetStoredProcCommand(storedProcName))
                {

                    if (spParams != null)
                    {
                        foreach (QueryParamObj obj in spParams)
                        {
                            db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                        }
                    }
                    ds = db.ExecuteDataSet(cmd);
                }

            }
            catch (Exception ex)
            {
                pErrString = ex.Message;
            }
            return ds;
        }

        protected void DBExecSPInOutEx(string storedProcName, ref QueryParamList spParams, int pCommandTimeout, ref string pErrString)
        {
            Database db = GetSQLDatabase();
            DbCommand cmd = db.GetStoredProcCommand(storedProcName);
            if (pCommandTimeout > 0)
            {
                cmd.CommandTimeout = pCommandTimeout;
            }

            if (spParams != null)
            {
                foreach (QueryParamObj obj in spParams)
                {
                    db.AddParameter(cmd, obj.ParamName, obj.DBType, obj.ParamDirection, null, DataRowVersion.Current, obj.ParamValue);
                }
            }
            try
            {
                db.ExecuteNonQuery(cmd);
            }
            catch (Exception ex) { pErrString = ex.Message; }
            finally
            {
                if (spParams != null)
                {
                    foreach (QueryParamObj obj in spParams)
                    {
                        obj.ParamValue = db.GetParameterValue(cmd, obj.ParamName);
                    }
                }
                CloseConnection(cmd);
            }
        }

        /// <summary>
        /// Executes a stored procedure on the live config db with parameters specifying the company
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="spParams"></param>
        /// <param name="pCompanyNumber"></param>
        protected void DBExecStoredProc(string storedProcName, QueryParamList spParams, ref string pErrString)
        {
            Database db = GetSQLDatabase();
            using (DbCommand cmd = db.GetStoredProcCommand(storedProcName))
            {
                if (spParams != null)
                {
                    foreach (QueryParamObj obj in spParams)
                    {
                        db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                    }
                }
                try
                {
                    db.ExecuteNonQuery(cmd);
                }
                catch (Exception ex)
                {
                    pErrString = ex.Message;
                    //throw;
                }
            }

        }

        /// <summary>
        /// Executes a stored procedure on the live config db with parameters specifying the company with transaction parameter to do transaction management
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="spParams"></param>
        /// <param name="pCompanyNumber"></param>
        /// <param name="pTransaction"></param>
        /// <returns></returns>
        protected int DBExecStoredProcInTran(Database db, DbCommand cmd, QueryParamList spParams, DbTransaction pTransaction)
        {
            if (spParams != null)
            {
                foreach (QueryParamObj obj in spParams)
                {
                    db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                }
            }

            return db.ExecuteNonQuery(cmd, pTransaction);

        }

        /// <summary>
        ///Private Metod that executes a sql command on specified db that does not return a resultset
        /// </summary>
        /// <param name="queryName"></param>
        /// <param name="spParams"></param>
        /// <param name="db"></param>
        /// <returns></returns>
        protected void DBExecCommandEx(string query, QueryParamList spParams, Database db, ref string pErrString)
        {
            try
            {
                using (DbCommand cmd = db.GetSqlStringCommand(query))
                {
                    if (spParams != null)
                    {
                        foreach (QueryParamObj obj in spParams)
                        {
                            db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                        }
                    }
                    db.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception e)
            {
                pErrString = e.Message;

            }
        }

        protected void DBExecCommandEx(string query, QueryParamList spParams, ref string pErrString)
        {
            Database db = GetSQLDatabase();
            DBExecCommandEx(query, spParams, db, ref pErrString);
        }

        protected int DBExecCommandExTran(string query, QueryParamList spParams, DbTransaction pTransaction, ref string pErrString)
        {
            Database db = GetSQLDatabase();

            DbCommand cmd = db.GetSqlStringCommand(query);
            if (spParams != null)
            {
                foreach (QueryParamObj obj in spParams)
                {
                    db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                }
            }
            return db.ExecuteNonQuery(cmd, pTransaction);
        }

        protected DataSet LoadDataSet(string query, QueryParamList spParams, string tableName, ref string pErrString)
        {
            Database db = GetSQLDatabase();
            DataSet ds = new DataSet();
            try
            {
                using (DbCommand cmd = db.GetSqlStringCommand(query))
                {
                    if (spParams != null)
                    {
                        foreach (QueryParamObj obj in spParams)
                        {
                            db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                        }
                    }
                    db.LoadDataSet(cmd, ds, tableName);
                }

            }
            catch (Exception e)
            {
                pErrString = e.Message;
            }
            return ds;
        }

        protected object DBExecuteScalar(string query, QueryParamList spParams, ref string pErrString)
        {
            Database db = GetSQLDatabase();
            object result = null;
            try
            {
                using (DbCommand cmd = db.GetSqlStringCommand(query))
                {
                    if (spParams != null)
                    {
                        foreach (QueryParamObj obj in spParams)
                        {
                            db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                        }
                    }
                    result = db.ExecuteScalar(cmd);
                }
            }
            catch (Exception e)
            {
                pErrString = e.Message;
            }
            return result;
        }

        protected object DBExecuteScalar(string query, QueryParamList spParams, DbTransaction pTransaction)
        {
            Database db = GetSQLDatabase();
            object result = null;

            using (DbCommand cmd = db.GetSqlStringCommand(query))
            {
                if (spParams != null)
                {
                    foreach (QueryParamObj obj in spParams)
                    {
                        db.AddInParameter(cmd, obj.ParamName, obj.DBType, obj.ParamValue);
                    }
                }
                result = db.ExecuteScalar(cmd, pTransaction);
            }
            return result;
        }

        DbCommand _command = null;
        /// <summary>
        /// Runs a query on DB with parameters and returns a dbDataReader. must close the reader after done with reader.
        /// </summary>
        /// <param name="queryName"></param>
        /// <param name="queryParams"></param>        
        /// <returns></returns>
        public DbDataReader GetDBQueryReader(string query, QueryParamList queryParams)
        {
            Database db = GetSQLDatabase();
            _command = db.GetSqlStringCommand(query);
            if (_command.Connection == null)
                _command.Connection = db.CreateConnection();
            if (queryParams != null)
            {
                foreach (QueryParamObj obj in queryParams)
                {
                    db.AddInParameter(_command, obj.ParamName, obj.DBType, obj.ParamValue);
                }
            }
            if (_command.Connection.State != ConnectionState.Open)
            {
                _command.Connection.Open();
            }
            return _command.ExecuteReader(CommandBehavior.SingleResult);
        }
        protected void CloseConnection()
        {
            if (_command != null && _command.Connection.State == ConnectionState.Open)
            {
                _command.Connection.Close();
            }
        }
        protected void CloseConnection(DbCommand command)
        {
            if (command != null && command.Connection.State == ConnectionState.Open)
            {
                command.Connection.Close();
            }
        }
    }
}
